How to rebuild the Content Studio full-text index

Author: Halfvares Mats, Teknikhuset AB.

Published: 2004-09-13

Applies to:

Content Studio ver. 4.0

Type: How to

More information

Sometimes it is neccessary to rebuild the Content Studio full text indexes. This i always needed when you have restored a Content Studio site database on another database server or on the same server but with another name. The reason for this is that the name of the full text catalogs containing the index is given a name that is dependent on the SQL Server ID of the database ie. "ftxt_DATABASEID_CS_ContentBinary" and "ftxt_DATABASEID_XML_Data" where DATABASEID is the internal id of the database. This ID is not preserved when you restore the database as a new database but the full-text information will remain. You also will note that the database reports that the full text catalog has been lost. In this situation you will need to delete the 2 catalogs and completly rebuild them.

To delete the catalogs

Open Enterprise Manager and expand the Full text folder of the site database
In the right pane right click each one of the 2 full text catalogs and select rebuild. Enterprise Manager will ask you if you want to loose the index and completly rebuild it - select Yes on this question
Right click on each one of the catalogs and select Delete. You might receive some error messages during the delete process but you can check the status by refreshing the view afterwards.
Both catalogs should be gone by now.

To rebuild the catalogs

Start the SQL Query analyser tool from the tools menu in Enterprise Manager and paste in the script that rebuilds the catalogs. Below is the links to the scripts that can be used. Make sure you download the correct script depending on the version installed.

Content Studio 4.0 build 1009 and earlier
Display code

-- Drop all CS fulltext index catalogs on the current database 
-- if they have the wrong name
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
  SELECT @Ver = 2005;
ELSE
  SELECT @Ver = 2000;

DECLARE @script nvarchar(4000);

--Drop all fulltext indexes if they exists
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_modules_content'),N'ContentBinary',N'IsFulltextIndexed') = 1
  BEGIN
     IF @Ver = 2005
        EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_modules_content;';
     ELSE
        EXECUTE sp_fulltext_table N'dbo.tbl_modules_content', N'drop';
  END
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_Xml_data'),N'FullData',N'IsFulltextIndexed') = 1
  BEGIN
     IF @Ver = 2005
        EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_Xml_data;';
     ELSE
        EXECUTE sp_fulltext_table N'dbo.tbl_Xml_data', N'drop';
  END

DECLARE @catalog sysname;
DECLARE Curs CURSOR FAST_FORWARD READ_ONLY  FOR
        SELECT [name] FROM dbo.sysfulltextcatalogs 
OPEN Curs;
FETCH NEXT FROM Curs INTO @catalog
WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @Ver = 2005
        SELECT @script = N'DROP FULLTEXT CATALOG [' + @Catalog + ']'

    ELSE
        SELECT @script = N'EXECUTE sp_fulltext_catalog [' + @Catalog + '], N''drop'';';

    EXECUTE sp_executesql @script;
    FETCH NEXT FROM Curs INTO @catalog
  END
CLOSE Curs;
DEALLOCATE Curs;

GO
--The content fulltext column
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
  SELECT @Ver = 2005;
ELSE
  SELECT @Ver = 2000;

DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_CS_ContentBinary';

IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE [name] = @Catalog)
BEGIN
    IF @Ver = 2005
        SELECT @script = N'CREATE FULLTEXT CATALOG [' + @Catalog + ']'

    ELSE
        SELECT @script = N'EXECUTE sp_fulltext_catalog [' + @Catalog + '], N''create'';';

    EXECUTE sp_executesql @script;
END

--drop the index if existing
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_modules_content'),N'ContentBinary',N'IsFulltextIndexed') = 1
  BEGIN
     IF @Ver = 2005
        EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_modules_content;';
     ELSE
        EXECUTE sp_fulltext_table N'dbo.tbl_modules_content', N'drop';
  END

GO
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
  SELECT @Ver = 2005;
ELSE
  SELECT @Ver = 2000;

DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_CS_ContentBinary';

IF @Ver = 2005
    BEGIN
        SELECT @script = N'
        CREATE FULLTEXT INDEX ON [dbo].[tbl_modules_content]
        (
          [ContentBinary] TYPE COLUMN [Fil_Ext] LANGUAGE ''Swedish'',
          [introduction] LANGUAGE ''Swedish'',
          [Keywords] LANGUAGE ''Swedish''
        )
        KEY INDEX [PK_tbl_modules_content] ON [' + @Catalog + '] WITH CHANGE_TRACKING MANUAL;';
        EXECUTE sp_executesql @script;
        SELECT @script = N'ALTER FULLTEXT INDEX ON [dbo].[tbl_modules_content] ENABLE;';
        EXECUTE sp_executesql @script;
    END
ELSE
    BEGIN
        EXECUTE sp_fulltext_table N'[dbo].[tbl_modules_content]', N'create', @Catalog, N'PK_tbl_modules_content'
        EXECUTE sp_fulltext_column N'[dbo].[tbl_modules_content]', N'introduction', N'add', 0
        EXECUTE sp_fulltext_column N'[dbo].[tbl_modules_content]', N'ContentBinary', N'add', 0, N'Fil_ext'
        EXECUTE sp_fulltext_table N'[dbo].[tbl_modules_content]', N'activate';
    END

GO

--The Xml-index fulltext column
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
  SELECT @Ver = 2005;
ELSE
  SELECT @Ver = 2000;

DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = N'ftxt_' + CAST(DB_ID(DB_NAME()) AS nvarchar(10)) + N'_XML_Data'

IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE [name] = @Catalog)
BEGIN
    IF @Ver = 2005
        SELECT @script = N'CREATE FULLTEXT CATALOG [' + @Catalog + ']' /*+
                         N'IN PATH ''E:\SQL Server 2005\FTDATA'';';*/
    ELSE
        SELECT @script = N'EXECUTE sp_fulltext_catalog [' + @Catalog + '], N''create'';';

    EXECUTE sp_executesql @script;
END

--drop the index if existing
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_Xml_data'),N'FullData',N'IsFulltextIndexed') = 1
  BEGIN
     IF @Ver = 2005
        EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_Xml_data;';
     ELSE
        EXECUTE sp_fulltext_table N'dbo.tbl_Xml_data', N'drop';
  END
GO
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
  SELECT @Ver = 2005;
ELSE
  SELECT @Ver = 2000;

DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_XML_Data';

IF @Ver = 2005
    BEGIN
        SELECT @script = N'
        CREATE FULLTEXT INDEX ON [dbo].[tbl_xml_data]
        (
          [FullData] LANGUAGE ''Swedish''
        )
        KEY INDEX [PK_tbl_xml_data] ON [' + @Catalog + '] WITH CHANGE_TRACKING AUTO;';
        EXECUTE sp_executesql @script;
        SELECT @script = N'ALTER FULLTEXT INDEX ON [dbo].[tbl_xml_data] ENABLE;';
        EXECUTE sp_executesql @script;
    END
ELSE
    BEGIN
        EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'create', @Catalog,  N'PK_tbl_xml_data';
        EXECUTE sp_fulltext_column N'[dbo].[tbl_xml_data]', N'FullData', N'add', 0;
        EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'activate';
        --start background index update
        EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'Start_change_tracking';
        EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'Start_background_updateindex';
    END

Studio 4.0 build 1010 and later.
Display code

-- Drop all CS fulltext index catalogs on the current database 
-- if they have the wrong name
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
  SELECT @Ver = 2005;
ELSE
  SELECT @Ver = 2000;

DECLARE @script nvarchar(4000);

--Drop all fulltext indexes if they exists
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_modules_content'),N'ContentBinary',N'IsFulltextIndexed') = 1
  BEGIN
     IF @Ver = 2005
        EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_modules_content;';
     ELSE
        EXECUTE sp_fulltext_table N'dbo.tbl_modules_content', N'drop';
  END
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_Xml_data'),N'FullData',N'IsFulltextIndexed') = 1
  BEGIN
     IF @Ver = 2005
        EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_Xml_data;';
     ELSE
        EXECUTE sp_fulltext_table N'dbo.tbl_Xml_data', N'drop';
  END

DECLARE @catalog sysname;
DECLARE Curs CURSOR FAST_FORWARD READ_ONLY  FOR
        SELECT [name] FROM dbo.sysfulltextcatalogs 
OPEN Curs;
FETCH NEXT FROM Curs INTO @catalog
WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @Ver = 2005
        SELECT @script = N'DROP FULLTEXT CATALOG [' + @Catalog + ']'

    ELSE
        SELECT @script = N'EXECUTE sp_fulltext_catalog [' + @Catalog + '], N''drop'';';

    EXECUTE sp_executesql @script;
    FETCH NEXT FROM Curs INTO @catalog
  END
CLOSE Curs;
DEALLOCATE Curs;

GO
--The content fulltext column
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
  SELECT @Ver = 2005;
ELSE
  SELECT @Ver = 2000;

DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_CS_ContentBinary';

IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE [name] = @Catalog)
BEGIN
    IF @Ver = 2005
        SELECT @script = N'CREATE FULLTEXT CATALOG [' + @Catalog + ']'

    ELSE
        SELECT @script = N'EXECUTE sp_fulltext_catalog [' + @Catalog + '], N''create'';';

    EXECUTE sp_executesql @script;
END

--drop the index if existing
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_modules_content'),N'ContentBinary',N'IsFulltextIndexed') = 1
  BEGIN
     IF @Ver = 2005
        EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_modules_content;';
     ELSE
        EXECUTE sp_fulltext_table N'dbo.tbl_modules_content', N'drop';
  END

GO
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
  SELECT @Ver = 2005;
ELSE
  SELECT @Ver = 2000;

DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_CS_ContentBinary';

IF @Ver = 2005
    BEGIN
        SELECT @script = N'
        CREATE FULLTEXT INDEX ON [dbo].[tbl_modules_content]
        (
          [ContentBinary] TYPE COLUMN [Fil_Ext] LANGUAGE ''Swedish'',
          [introduction] LANGUAGE ''Swedish'',
          [Keywords] LANGUAGE ''Swedish''
        )
        KEY INDEX [PK_tbl_modules_content] ON [' + @Catalog + '] WITH CHANGE_TRACKING MANUAL;';
        EXECUTE sp_executesql @script;
        SELECT @script = N'ALTER FULLTEXT INDEX ON [dbo].[tbl_modules_content] ENABLE;';
        EXECUTE sp_executesql @script;
    END
ELSE
    BEGIN
        EXECUTE sp_fulltext_table N'[dbo].[tbl_modules_content]', N'create', @Catalog, N'PK_tbl_modules_content'
        EXECUTE sp_fulltext_column N'[dbo].[tbl_modules_content]', N'introduction', N'add', 0
        EXECUTE sp_fulltext_column N'[dbo].[tbl_modules_content]', N'Keywords', N'add', 0
        EXECUTE sp_fulltext_column N'[dbo].[tbl_modules_content]', N'ContentBinary', N'add', 0, N'Fil_ext'
        EXECUTE sp_fulltext_table N'[dbo].[tbl_modules_content]', N'activate';
    END

GO

--The Xml-index fulltext column
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
  SELECT @Ver = 2005;
ELSE
  SELECT @Ver = 2000;

DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = N'ftxt_' + CAST(DB_ID(DB_NAME()) AS nvarchar(10)) + N'_XML_Data'

IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE [name] = @Catalog)
BEGIN
    IF @Ver = 2005
        SELECT @script = N'CREATE FULLTEXT CATALOG [' + @Catalog + ']' /*+
                         N'IN PATH ''E:\SQL Server 2005\FTDATA'';';*/
    ELSE
        SELECT @script = N'EXECUTE sp_fulltext_catalog [' + @Catalog + '], N''create'';';

    EXECUTE sp_executesql @script;
END

--drop the index if existing
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_Xml_data'),N'FullData',N'IsFulltextIndexed') = 1
  BEGIN
     IF @Ver = 2005
        EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_Xml_data;';
     ELSE
        EXECUTE sp_fulltext_table N'dbo.tbl_Xml_data', N'drop';
  END
GO
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
  SELECT @Ver = 2005;
ELSE
  SELECT @Ver = 2000;

DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog  = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_XML_Data';

IF @Ver = 2005
    BEGIN
        SELECT @script = N'
        CREATE FULLTEXT INDEX ON [dbo].[tbl_xml_data]
        (
          [FullData] LANGUAGE ''Swedish''
        )
        KEY INDEX [PK_tbl_xml_data] ON [' + @Catalog + '] WITH CHANGE_TRACKING AUTO;';
        EXECUTE sp_executesql @script;
        SELECT @script = N'ALTER FULLTEXT INDEX ON [dbo].[tbl_xml_data] ENABLE;';
        EXECUTE sp_executesql @script;
    END
ELSE
    BEGIN
        EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'create', @Catalog,  N'PK_tbl_xml_data';
        EXECUTE sp_fulltext_column N'[dbo].[tbl_xml_data]', N'FullData', N'add', 0;
        EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'activate';
        --start background index update
        EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'Start_change_tracking';
        EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'Start_background_updateindex';
    END

Execute the script and close Entererprise Manager and Query Analyzer.

You should now be able to work with the Content Studio full-text index as usual.